﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using sqlserverextensions.ExportOperations;
using System.IO;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static void QueryToHtml(SqlString fileName,
                                   SqlString sqlStatement,
                                   SqlBoolean multiQuery,
                                   SqlBoolean columnHeader,
                                   SqlString title)
    {


        TextWriter strWriter;

        //' Validieren der Übergabe-Parameter
        ExportHelper.ValidateMandatoryParameters(fileName, sqlStatement, multiQuery, columnHeader);

        if (multiQuery)
        {
            DataSet dataSet = ExportHelper.GetDataSet(sqlStatement.ToString());

            strWriter = new StreamWriter(fileName.ToString(), false);
            strWriter.Write(ExportHelper.GetPageHeaderHtml(title.ToString()));
            foreach (DataTable dt in dataSet.Tables)
            {
                ExportHelper.Export2Html(dt, ref strWriter, columnHeader.Value);
                strWriter.Flush();
                if (dataSet.Tables[dataSet.Tables.Count - 1] != null)
                    //' Eine Leerzeile Abstand bei neuer Tabelle.
                    strWriter.WriteLine("<p>&nbsp;</p>");

            }
            strWriter.Write(ExportHelper.GetPageFooterHtml());
            strWriter.Close();
            strWriter.Dispose();

            dataSet.Dispose();
        }
        else
        {
            using (SqlConnection connection = new SqlConnection("context connection=true"))
            {
                connection.Open();

                SqlDataReader dataReader = ExportHelper.GetDataReader(connection, sqlStatement.ToString());

                strWriter = new StreamWriter(fileName.ToString(), false);
                strWriter.Write(ExportHelper.GetPageHeaderHtml(title.ToString()));
                ExportHelper.Export2Html(ref dataReader, ref strWriter, columnHeader.Value);
                strWriter.Flush();
                strWriter.Write(ExportHelper.GetPageFooterHtml());
                strWriter.Close();
                strWriter.Dispose();
                dataReader.Close();
                connection.Close();
            }

        }

    }
};
